package com.oas.web.json;

import java.net.URLDecoder;

import org.apache.log4j.Logger;

import com.oas.common.Config;
import com.oas.common.DBCache;
import com.oas.common.DataSourceAction;
import com.oas.common.Helper;
import com.oas.common.Utils;
import com.oas.objects.Financial;
import com.oas.objects.Order;
import com.oas.objects.Query;
import com.oas.util.StringUtil;

public class FinancialJson extends DataSourceAction {

	private static final long serialVersionUID = -5612721889737285900L;
	protected final Logger log = Logger.getLogger(getClass());

	private Query query;
	private Financial financial;
	private Order order;
	private String workno;
	private String clientid;
	
	public Query getQuery() {
		return query;
	}
	
	public void setQuery(Query query) {
		this.query = query;
	}

	public Financial getFinancial() {
		return financial;
	}

	public void setFinancial(Financial financial) {
		this.financial = financial;
	}

	public Order getOrder() {
		return order;
	}

	public void setOrder(Order order) {
		this.order = order;
	}

	public String getWorkno() {
		return workno;
	}

	public void setWorkno(String workno) {
		this.workno = workno;
	}

	public String getClientid() {
		return clientid;
	}

	public void setClientid(String clientid) {
		this.clientid = clientid;
	}

	public String execute() throws Exception {
		try {
			if (action != null) {
				skip = SUCCESS;
				String userid = (String) getSession().getAttribute(Config.USER_ID);
				String workno = (String) getSession().getAttribute(Config.USER_WORKNO);
				if ("carryOver".equals(action)) {
					Double recm = order.getRecmoney();
					Double recmNew = -recm;
					sql = "insert into customer_transfer" +
							"(transfer_id,customer_id,transfer_account" +
							",transfer_amount,usable_amount,usable_state,remark,transfer_type,transfer_state,creater" +
							",create_time,create_timei) values("
							+ DBCache.getSequence()
							+ ",'"
							+ clientid
							+ "','退票结转',"
							+ recmNew
							+ ","+recmNew+",0,'退票结转',3,2,'"
							+ workno
							+ "',"
							+"'"+Utils.getNowTimestamp()+ "',"+Utils.getSystemMillis()+")";
					System.out.println(sql);
					executeUpdate(sql);
					sql = "update financial_order set received_state = 3,received_amount = "+order.getRecmoney()+" where financial_order_code = "+financial.getFinancialOrderCode()+" ";
					executeUpdate(sql);
					sql = "insert into customer_amount_logs (customer_id,rela_id" +
					",type,residualValueOld,residualValueNew,paid_amount_old" +
					",paid_amount_new,lasttime,lasttimei,cmnt) " +
					"select c.customerID,'"+financial.getFinancialOrderCode()+"'" +
					",5,c.residualValue,c.residualValue - ("+order.getRecmoney()+")" +
					",c.paid_amount,c.paid_amount+ ("+order.getRecmoney()+"),'"+Utils.getNowTimestamp()+ "',"+Utils.getSystemMillis()+",'退票结转金额："+order.getRecmoney()+"' " +
					"FROM customer c WHERE c.customerID = '"+clientid+"' ";
					executeUpdate(sql);
					sql = "update customer set residualValue = residualValue - ("+order.getRecmoney()+"),paid_amount = paid_amount + ("+order.getRecmoney()+") where customerID = '"+clientid+"'";
					executeUpdate(sql);
					
					queryList();
				}
				
			} else {
				queryList();
			}
		} catch (Exception e) {
			skip = ERROR;
			addActionError(e.toString());
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
			
		return skip;
	}
	
	public void queryList() throws Exception{
		term = Helper.getQuerySqlNull(query, "fo.create_timei");

		skip = JSON;
		String sqlStr = "select fo.financial_order_code,fo.order_code,fo.adjust_profit,fo.order_state" +
				",fo.received_amount,fo.received_state,oi.pnr,oi.recmoney" +
				",oi.paymoney,oi.profit,oi.type,fo.creater,fo.last_user" +
				",fo.create_time,fo.last_operate_time,fo.order_state" +
				",fo.audit_state,u1.name userName,u2.name luserName,oi.cid,c.name,c.company" +
				" from financial_order fo" +
				" inner join orderinfo oi on fo.order_code = oi.orderid" +
				" left join user u1 on fo.creater = u1.workcardno" +
				" left join user u2 on fo.last_user = u2.workcardno" +
				" left join customer c on c.customerID = oi.cid" +
				" where 1=1 " + term;
		if(!"".equals(financial.getOrderCode())&&financial.getOrderCode()!=null){
			sqlStr += " and fo.order_code in ('"+financial.getOrderCode().trim()+"')";
		}
		if(!"".equals(financial.getOrderState())&&financial.getOrderState()!=null){
			sqlStr += " and fo.order_state = "+financial.getOrderState()+"";
		}
		if((!"".equals(order.getPnr())&&order.getPnr()!=null)||(order.getType()!=0&&order.getType()!=99)||(!"".equals(order.getClientid())&&order.getClientid()!=null)){
			sqlStr += " and exists (select 1 from orderinfo o where 1=1 and o.orderid = fo.order_code ";
			if(!"".equals(order.getPnr())&&order.getPnr()!=null){
				String pnr = URLDecoder.decode(order.getPnr().trim(),"UTF-8");
				sqlStr += " and o.pnr like '%"+pnr+"%'";
			}
			if(order.getType()!=0&&order.getType()!=99){
				sqlStr += " and o.type ="+order.getType()+"";
			}
			if(!"".equals(order.getClientid())&&order.getClientid()!=null){
				sqlStr += " and o.cid like '%"+order.getClientid().trim()+"%'";
			}
			sqlStr +=")";
		}
//		if(!"".equals(order.getCustname())&&order.getCustname()!=null){
//			String cname = URLDecoder.decode(order.getCustname().trim(),"UTF-8");
//			sqlStr += " and exists (select 1 from customer ct where 1=1 and ct.customerID = oi.cid and ct.name like '%"+cname+"%') ";
//		}
		if(!"".equals(order.getCustcompany())&&order.getCustcompany()!=null){
			String custcompany = URLDecoder.decode(order.getCustcompany().trim(),"UTF-8");
			sqlStr += " and exists (select 1 from customer ct where 1=1 and ct.customerID = oi.cid and ct.company like '%"+custcompany+"%') ";
		}
		if(!"".equals(workno)&&workno!=null){
			sqlStr += " and oi.userid like '%"+workno.trim()+"%'";
		}
		if(financial.getReceivedState()!=null&&financial.getReceivedState()!=99){
			sqlStr += " and fo.received_state ="+financial.getReceivedState()+"";
		}
		if(financial.getAuditState()!=null&&financial.getAuditState()!=99){
			sqlStr += " and fo.audit_state ="+financial.getAuditState()+"";
		}
		
		sqlStr += " order by fo.financial_order_code desc";
		System.out.println(sqlStr);
		executeQuery(sqlStr);
		float recmoneys = 0;
		float paymoneys = 0;
		float profits = 0;
		float adjustProfits = 0;
		float receivedAmounts = 0;
		while (rs.next()) {
			Long financialOrderCode = rs.getLong("financial_order_code");
			String orderCode = rs.getString("order_code");
			String pnr = rs.getString("pnr");
			Float recmoney = rs.getFloat("recmoney");
			Float paymoney = rs.getFloat("paymoney");
			Float profit = rs.getFloat("profit");
			Float adjustProfit = rs.getFloat("adjust_profit");
			Float receivedAmount = rs.getFloat("received_amount");
			recmoneys += Float.valueOf(recmoney);
			paymoneys += Float.valueOf(paymoney);
			profits += Float.valueOf(profit);
			adjustProfits += Float.valueOf(adjustProfit);
			receivedAmounts += Float.valueOf(receivedAmount);
			int receiveState = rs.getInt("received_state");
			int orderType = rs.getInt("type");
			int orderState = rs.getInt("order_state");
			int auditState = rs.getInt("audit_state");
			String cid = rs.getString("cid");
			String name = rs.getString("name");
			String company = rs.getString("company");
			String user = StringUtil.toString(rs.getString("creater"));
			String luser = StringUtil.toString(rs.getString("last_user"));
			String userName = StringUtil.toString(rs.getString("userName"));
			String luserName = StringUtil.toString(rs.getString("luserName"));
			String createTime = StringUtil.toString(rs.getDate("create_time"))+" "+StringUtil.toString(rs.getTime("create_time"));
			String updateTime = StringUtil.toString(rs.getDate("last_operate_time"))+" "+StringUtil.toString(rs.getTime("last_operate_time"));
			if (count != 0)
				bf.append(",");
			bf.append("{'financialOrderCode':'" + financialOrderCode + "'")
			  .append(",'orderCode':'" + orderCode + "'")
			  .append(",'pnr':'" + pnr + "'")
			  .append(",'receivedAmount':'" + receivedAmount + "'")
			  .append(",'orderType':'" + orderType + "'")
			  .append(",'receiveState':'" + receiveState + "'")
			  .append(",'orderState':'" + orderState + "'")
			  .append(",'auditState':'" + auditState + "'")
			  .append(",'recmoney':'" + recmoney + "'")
			  .append(",'paymoney':'" + paymoney + "'")
			  .append(",'profit':'" + profit + "'")
			  .append(",'adjustProfit':'" + adjustProfit + "'")
			  .append(",'user':'" + user + "'")
			  .append(",'userName':'" + userName + "'")
			  .append(",'createTime':'" + createTime + "'")
			  .append(",'cid':'" + cid + "'")
			  .append(",'name':'" + name + "'")
			  .append(",'company':'" + company + "'")
			  .append(",'luser':'" + luser + "'")
			  .append(",'luserName':'" + luserName + "'")
			  .append(",'updateTime':'" + updateTime + "'}");
			count++;
		}
		String counts = "总数：" + count+", 总应收款：" + String.format("%.2f", recmoneys) + " , 总应付款：" + String.format("%.2f", paymoneys) + " , 总利润：" + String.format("%.2f", profits)+ " , 总校正利润：" + String.format("%.2f", adjustProfits)+" ,总收款：" + String.format("%.2f", receivedAmounts);
		tojsonNew(counts);
	}
	
}
